package system;
import java.sql.*;
public class getTestList{
	String user_id,test_id;
	String testIdList[];
	int count;
	
	public void setUserId(String s){
		user_id=s.trim();
	}
	public String getUserId(){
		return user_id;
	}
	
	public int getStudentCount(){
		Connection con;
	    Statement sql;
	    ResultSet rs;
	    try{
	    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	    	}
	    catch(Exception e){}
   
        try{
		String uri = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test_system";
		String user = "sa";
		String password = "sa";
		con=DriverManager.getConnection(uri,user,password);
		sql=con.createStatement();
	    rs=sql.executeQuery("select COUNT(*) from student_test where student_id='"+user_id+"'");
	    rs.next();
	    count=rs.getInt(1);
		con.close();
	    } catch(SQLException e1){}
	    return count;
	}
	
	public int getTeacherCount(){
		Connection con;
	    Statement sql;
	    ResultSet rs;
	    try{
	    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	    	}
	    catch(Exception e){}
   
        try{
		String uri = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test_system";
		String user = "sa";
		String password = "sa";
		con=DriverManager.getConnection(uri,user,password);
		sql=con.createStatement();
	    rs=sql.executeQuery("select COUNT(*) from testing where opeater_id='"+user_id+"'");
	    rs.next();
	    count=rs.getInt(1);
		con.close();
	    } catch(SQLException e1){}
	    return count;
	}
	
	public String[] getStudentTestIdList(){
		int index=0;
		Connection con;
	    Statement sql;
	    ResultSet rs;
	    testIdList=new String[count];
	    try{
	    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	    	}
	    catch(Exception e){}
   
        try{
		String uri = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test_system";
		String user = "sa";
		String password = "sa";
		con=DriverManager.getConnection(uri,user,password);
		sql=con.createStatement();
		rs=sql.executeQuery("SELECT * from student_test where student_id="+"'"+user_id+"'");
		while(rs.next()){
			testIdList[index]=rs.getString(2);	
			index+=1;		
		}
		con.close();
	    } catch(SQLException e1){}
	    return testIdList;
	}
	
	public String[] getTeacherTestIdList(){
		int index=0;
		Connection con;
	    Statement sql;
	    ResultSet rs;
	    testIdList=new String[count];
	    try{
	    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	    	}
	    catch(Exception e){}
   
        try{
		String uri = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test_system";
		String user = "sa";
		String password = "sa";
		con=DriverManager.getConnection(uri,user,password);
		sql=con.createStatement();
		rs=sql.executeQuery("SELECT * from testing where opeater_id="+"'"+user_id+"'");
		while(rs.next()){
			testIdList[index]=rs.getString(1);	
			index+=1;		
		}
		con.close();
	    } catch(SQLException e1){}
	    return testIdList;
	}
	
	public String getTestName(String testid){
		String testName="";
		Connection con;
	    Statement sql;
	    ResultSet rs;
	    try{
	    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	    	}
	    catch(Exception e){}
   
        try{
		String uri = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test_system";
		String user = "sa";
		String password = "sa";
		con=DriverManager.getConnection(uri,user,password);
		sql=con.createStatement();
		rs=sql.executeQuery("SELECT * from testing where test_id="+"'"+testid+"'");
		if(rs.next()){
			testName=rs.getString(3);			
		}
		con.close();
	    } catch(SQLException e1){}
	    return testName;
	}
	
	public String getOpeaterName(String testid){
		String opeaterName="";
		Connection con;
	    Statement sql;
	    ResultSet rs;
	    try{
	    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	    	}
	    catch(Exception e){}
   
        try{
		String uri = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test_system";
		String user = "sa";
		String password = "sa";
		con=DriverManager.getConnection(uri,user,password);
		sql=con.createStatement();
		rs=sql.executeQuery("select user_name from testing,user_inf where testing.test_id='"+testid+"' and testing.opeater_id=user_inf.user_id");
		if(rs.next()){
			opeaterName=rs.getString(1);			
		}
		con.close();
	    } catch(SQLException e1){}
	    return opeaterName;
	}
	
	public int getIsFinished(String studentid,String testid){
		int isFinished=0;
		Connection con;
	    Statement sql;
	    ResultSet rs;
	    try{
	    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	    	}
	    catch(Exception e){}
   
        try{
		String uri = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test_system";
		String user = "sa";
		String password = "sa";
		con=DriverManager.getConnection(uri,user,password);
		sql=con.createStatement();
		rs=sql.executeQuery("select isFinished from student_test where test_id='"+testid+"' and student_id='"+studentid+"'");
		if(rs.next()){
			isFinished=rs.getInt(1);			
		}
		con.close();
	    } catch(SQLException e1){}
	    return isFinished;
	}
}